﻿--created 07/05/2011 by levinhha
/*
USE master;
GO
EXEC sp_detach_db @dbname = N'QLXT_DB';
GO
*/

/*
USE master;
GO
CREATE DATABASE QLXT_DB 
    ON (FILENAME = 'C:\Users\levinhha\Desktop\QLXT\MainProject\QLXT_DB.mdf'),
    (FILENAME = 'C:\Users\levinhha\Desktop\QLXT\MainProject\QLXT_DB_Log.ldf')
    FOR ATTACH;
GO
*/


/*
USE master;
GO
CREATE DATABASE QLXT_DB
ON 
( NAME = QLXT_DB_DATA,
    FILENAME = 'C:\Users\levinhha\Desktop\QLXT\MainProject\QLXT_DB.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = QLXT_DB_LOG,
    FILENAME = 'C:\Users\levinhha\Desktop\QLXT\MainProject\QLXT_DB_Log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO


*/


USE QLXT_DB
GO

--=====================================tình trạng xe================================
IF OBJECT_ID('TINHTRANG', 'U') IS NOT NULL
DROP TABLE TINHTRANG
GO

CREATE TABLE TINHTRANG
(
	IDTINHTRANG int NOT NULL IDENTITY,
	TINHTRANG nvarchar(255),
	GHICHU ntext,
	PRIMARY KEY (IDTINHTRANG)
)


--=======================================hiệu xe==================================
IF OBJECT_ID('HIEUXE', 'U') IS NOT NULL
DROP TABLE HIEUXE
GO

CREATE TABLE HIEUXE
(
	IDHIEUXE int NOT NULL IDENTITY,
	HIEUXE nvarchar(255),
	GHICHU ntext,
	PRIMARY KEY (IDHIEUXE)
)


--========================================hạng xe===============================
IF OBJECT_ID('HANGXE', 'U') IS NOT NULL
DROP TABLE HANGXE
GO

CREATE TABLE HANGXE
(
	IDHANGXE int NOT NULL IDENTITY,
	HANGXE nvarchar(255),
	GHICHU ntext,
	PRIMARY KEY (IDHANGXE)
)


--========================================trạm đỗ=================================
IF OBJECT_ID('TRAM', 'U') IS NOT NULL
DROP TABLE TRAM
GO

CREATE TABLE TRAM
(
	IDTRAM int NOT NULL IDENTITY,
	TENTRAM nvarchar(255),
	DIACHI nvarchar(255),
	DIENTHOAI nvarchar(50),
	GHICHU ntext,
	PRIMARY KEY (IDTRAM)
)


--======================================tài xế=======================================
IF OBJECT_ID('TAIXE', 'U') IS NOT NULL
DROP TABLE TAIXE
GO

CREATE TABLE TAIXE
(
	IDTAIXE int NOT NULL IDENTITY,
	TEN nvarchar(50),
	NAMSINH datetime,
	NAMVAONGHE datetime,
	HANGXETHUONGLAI int NOT NULL,
	LUONGLAICHINH float,
	LUONGLAIPHU float,
	PHONE nvarchar(50),
	DIACHI nvarchar(255),
	NGHIVIEC bit,
	GHICHU ntext,
	PRIMARY KEY (IDTAIXE)
)


--=======================================xe========================================
IF OBJECT_ID('XE', 'U') IS NOT NULL
DROP TABLE XE
GO
CREATE TABLE [dbo].[XE]
(
	IDXE int NOT NULL IDENTITY,
	BIENSO nvarchar(20) NOT NULL,
	TRAMDAU int NOT NULL,
	HANGXE int NOT NULL,
	HIEUXE int NOT NULL,
	GIAYPHEPDANGKY nvarchar(255) NOT NULL,
	GIAYPHEPDANGKYMOR nvarchar(255) NOT NULL,
	NGAYDANGKY datetime,
	TINHTRANG int,
	GHICHU ntext,
	PRIMARY KEY (IDXE),
	FOREIGN KEY (TRAMDAU) REFERENCES TRAM(IDTRAM),
	FOREIGN KEY (HANGXE) REFERENCES HANGXE(IDHANGXE),
	FOREIGN KEY (HIEUXE) REFERENCES HIEUXE(IDHIEUXE),
	FOREIGN KEY (TINHTRANG) REFERENCES TINHTRANG(IDTINHTRANG)
)


--=====================================Chuyến=======================================
IF OBJECT_ID('CHUYEN', 'U') IS NOT NULL
DROP TABLE CHUYEN
GO


CREATE TABLE CHUYEN
(
	IDCHUYEN int NOT NULL IDENTITY,
	TENCHUYEN nvarchar(255),
	GHICHU ntext,
	TIENUNG	float,
	PRIMARY KEY (IDCHUYEN)
)

--==================================vận chuyển (điều hành xe)=======================
IF OBJECT_ID('VANCHUYEN', 'U') IS NOT NULL
DROP TABLE VANCHUYEN
GO

CREATE TABLE VANCHUYEN
(
	IDVANCHUYEN int NOT NULL,
	NGAYXUATXE datetime NOT NULL,
	TAIXECHINH int NOT NULL,
	TAIXEPHU int NOT NULL,
	XE int,
	DAUDUOCCAP float,
	CHUYEN int NOT NULL,
	NGAYVETRAM datetime,
	GHICHU ntext,
	PRIMARY KEY (IDVANCHUYEN),
	FOREIGN KEY (TAIXECHINH) REFERENCES TAIXE(IDTAIXE),
	FOREIGN KEY (TAIXEPHU) REFERENCES TAIXE(IDTAIXE),
	FOREIGN KEY (XE) REFERENCES XE(IDXE),
	FOREIGN KEY (CHUYEN) REFERENCES CHUYEN(IDCHUYEN)
)



--=========================================loại hàng hóa===============================
IF OBJECT_ID('LOAIHANGHOA', 'U') IS NOT NULL
DROP TABLE LOAIHANGHOA
GO

CREATE TABLE LOAIHANGHOA
(
	IDLOAIHANGHOA int NOT NULL IDENTITY,
	TENLOAIHANG nvarchar(255) NOT NULL,
	GHICHU ntext,
	PRIMARY KEY (IDLOAIHANGHOA)
)


--=====================================khách hàng====================================
IF OBJECT_ID('KHACHHANG','U') IS NOT NULL
DROP TABLE KHACHHANG
GO
CREATE TABLE [dbo].[KHACHHANG]
(
	IDKHACHHANG int NOT NULL IDENTITY,
	TENKHACHHANG nvarchar(50) NULL,
	DIACHI nvarchar(255) NULL,
	PHONE nvarchar(50) NULL,
	EMAIL nvarchar(50) NULL,
	GHICHU ntext NULL,
	PRIMARY KEY (IDKHACHHANG)
)

--============================================hàng hóa==============================
IF OBJECT_ID('DONVI','U') IS NOT NULL
DROP TABLE DONVI
GO

CREATE TABLE DONVI
(
	IDDONVI int NOT NULL IDENTITY,
	TENDONVI nvarchar(255),
	GHICHU ntext,
	PRIMARY KEY (IDDONVI)
)

--============================================hàng hóa==============================
IF OBJECT_ID('HANGHOA','U') IS NOT NULL
DROP TABLE HANGHOA
GO

CREATE TABLE HANGHOA 
(
	IDHANGHOA int NOT NULL IDENTITY,
	TENHANG nvarchar(255),
	SOLUONG int NOT NULL DEFAULT 0,
	DONVI int NOT NULL DEFAULT 1,
	MATHANG int NOT NULL,
	CHUHANG int NOT NULL,
	GIATRIHANGHOA float DEFAULT 0,
	GIATRIVANCHUYEN float,
	NGAYBOCHANG datetime NOT NULL,
	NGAYTRAHANG datetime NOT NULL,
	TRONGLUONG float NOT NULL DEFAULT 0,
	DAVANCHUYEN float NOT NULL DEFAULT 0,
	GHICHU ntext,
	PRIMARY KEY (IDHANGHOA),
	FOREIGN KEY (MATHANG) REFERENCES LOAIHANGHOA(IDLOAIHANGHOA),
	FOREIGN KEY (CHUHANG) REFERENCES KHACHHANG(IDKHACHHANG),
	FOREIGN KEY (DONVI) REFERENCES DONVI(IDDONVI)
)


--==========================================tuyến====================================
IF OBJECT_ID('TUYEN','U') IS NOT NULL
DROP TABLE TUYEN
GO

CREATE TABLE TUYEN
(
	IDTUYEN int NOT NULL IDENTITY,
	TRAMDAU int NOT NULL,
	TRAMCUOI int NOT NULL,
	TENTUYEN nvarchar(255) NOT NULL,
	CHIPHICAUDUONG float NOT NULL DEFAULT 0,
	GHICHU ntext,
	PRIMARY KEY (IDTUYEN),
	FOREIGN KEY (TRAMDAU) REFERENCES TRAM(IDTRAM),
	FOREIGN KEY (TRAMCUOI) REFERENCES TRAM(IDTRAM)
)


--=======================================chi phí xăng dầu===============================
IF OBJECT_ID('CHIPHIXANGDAU','U') IS NOT NULL
DROP TABLE CHIPHIXANGDAU
GO

CREATE TABLE CHIPHIXANGDAU
(
	IDCHIPHIXANGDAU int NOT NULL IDENTITY,
	IDXE int NOT NULL,
	HOADONBANLE nvarchar(255),
	SOLIT float NOT NULL,
	DONGIA float NOT NULL,
	TONGTIEN float DEFAULT 0,
	NGUOIDO int NOT NULL,
	NGAYDO datetime,
	CAYXANG nvarchar(255),
	TUYEN int NOT NULL DEFAULT -1,
	GHICHU ntext,
	PRIMARY KEY (IDCHIPHIXANGDAU),
	FOREIGN KEY (IDXE) REFERENCES XE(IDXE),
	FOREIGN KEY (NGUOIDO) REFERENCES TAIXE(IDTAIXE)
)


--========================================chi phí nhớt==============================

IF OBJECT_ID('CHIPHINHOT','U') IS NOT NULL
DROP TABLE CHIPHINHOT
GO

CREATE TABLE CHIPHINHOT
(
	IDCHIPHINHOT int NOT NULL IDENTITY,
	IDXE int NOT NULL,
	SOLIT float NOT NULL,
	DONGIA float NOT NULL,
	TONGTIEN float DEFAULT 0,
	NGAYDO datetime,
	TUYEN int NOT NULL DEFAULT -1,
	GHICHU ntext,
	PRIMARY KEY (IDCHIPHINHOT),
	FOREIGN KEY (IDXE) REFERENCES XE(IDXE)
)

--==========================chi phí tuyến xe======================================
IF OBJECT_ID('CHIPHITUYEN','U') IS NOT NULL
DROP TABLE CHIPHITUYEN
GO

CREATE TABLE CHIPHITUYEN
(
	IDTUYEN int NOT NULL,
	IDXE int NOT NULL,
	CHIPHICA float NOT NULL DEFAULT 0,
	CHIPHIAN float NOT NULL DEFAULT 0,
	CHIPHICAUDUOWNG float NOT NULL DEFAULT 0,
	CHIPHIXANGDAU float NOT NULL DEFAULT 0,
	CHIPHISUA float NOT NULL DEFAULT 0,
	CHIPHIKHAC float NOT NULL DEFAULT 0,
	CHIPHIHANGHOA float NOT NULL DEFAULT 0,
	GHICHU ntext,
	PRIMARY KEY (IDTUYEN,IDXE),
	FOREIGN KEY (IDTUYEN) REFERENCES TUYEN(IDTUYEN),
	FOREIGN KEY (IDXE) REFERENCES XE(IDXE)
)


--=============================danh sách hàng hóa=================================
IF OBJECT_ID('DANHSACHHANGHOA','U') IS NOT NULL
DROP TABLE DANHSACHHANGHOA
GO

CREATE TABLE DANHSACHHANGHOA
(
	IDDANHSACHHANGHOA int NOT NULL IDENTITY,
	IDHANGHOA int NOT NULL,
	IDXE int NOT NULL,
	SOLUONG int,
	TRONGLUONG float,
	TONGTIEN float,
	GHICHU ntext,
	PRIMARY KEY (IDDANHSACHHANGHOA),
	FOREIGN KEY (IDHANGHOA) REFERENCES HANGHOA(IDHANGHOA),
	FOREIGN KEY (IDXE) REFERENCES XE(IDXE)
)


--================================chi phí sửa xe===================================
IF OBJECT_ID('CHIPHISUA','U') IS NOT NULL
DROP TABLE CHIPHISUA
GO

CREATE TABLE CHIPHISUA
(
	IDCHIPHISUA int NOT NULL IDENTITY,
	IDXE int NOT NULL,
	GARA nvarchar(255),
	NGUYENNHAN nvarchar(255),
	NGAYSUA datetime,
	TUYEN int NULL,
	TONGTIEN float,
	GHICHU ntext,
	PRIMARY KEY (IDCHIPHISUA),
	FOREIGN KEY (IDXE) REFERENCES XE(IDXE)
)

--=====================================chi phí khác=======================================
IF OBJECT_ID('CHIPHIKHAC','U') IS NOT NULL
DROP TABLE CHIPHIKHAC
GO

CREATE TABLE CHIPHIKHAC
(
	IDCHIPHIKHAC int NOT NULL IDENTITY,
	TENCHIPHI nvarchar(255),
	GHICHU ntext,
	PRIMARY KEY (IDCHIPHIKHAC)
)


--=====================================danh sách chi phí khác=======================================
IF OBJECT_ID('DANHSACHCHIPHIKHAC','U') IS NOT NULL
DROP TABLE DANHSACHCHIPHIKHAC
GO

CREATE TABLE DANHSACHCHIPHIKHAC
(
	IDCHIPHIKHAC int NOT NULL IDENTITY,
	IDXE int NOT NULL,
	IDTUYEN	int NOT NULL,
	SOTIEN nvarchar(255),
	NGAYCHI datetime,
	GHICHU ntext,
	PRIMARY KEY (IDCHIPHIKHAC,IDXE,IDTUYEN),
	FOREIGN KEY (IDTUYEN) REFERENCES TUYEN(IDTUYEN),
	FOREIGN KEY (IDXE) REFERENCES XE(IDXE),
	FOREIGN KEY (IDCHIPHIKHAC) REFERENCES CHIPHIKHAC(IDCHIPHIKHAC)
)



--=====================================danh sách tuyến trong chuyến=======================================
IF OBJECT_ID('DANHSACHTUYEN','U') IS NOT NULL
DROP TABLE DANHSACHTUYEN
GO

CREATE TABLE DANHSACHTUYEN
(
	IDTUYEN	int NOT NULL,
	IDCHUYEN int NOT NULL,
	SOTHUTU int NOT NULL

	PRIMARY KEY (IDTUYEN, IDCHUYEN),
	FOREIGN KEY (IDTUYEN) REFERENCES TUYEN(IDTUYEN),
	FOREIGN KEY (IDCHUYEN) REFERENCES CHUYEN(IDCHUYEN)
)